﻿// 引入必要命名空间
using NPOI.SS.UserModel;
using System.Data;
using System.IO;
using System.Text;
using Ude;
public static class ExcelHelper
{
    public static Encoding DetectFileEncoding(string filePath)
    {
        using (var fs = File.OpenRead(filePath))
        {
            CharsetDetector detector = new CharsetDetector();
            detector.Feed(fs);
            detector.DataEnd();
            if (detector.Charset != null)
            {
                return Encoding.GetEncoding(detector.Charset);
            }
            else
            {
                // 检测失败，默认返回 UTF8
                return Encoding.UTF8;
            }
        }
    }
    /// <summary>
    /// 读取Excel或CSV文件并转换为指定类型的列表
    /// </summary>
    /// <typeparam name="T">目标实体类型</typeparam>
    /// <param name="filePath">文件路径（支持xls/xlsx/csv）</param>
    /// <returns>实体列表</returns>
    public static DataTable ReadExcelToList(string filePath)
    {
        var ext = Path.GetExtension(filePath).ToLowerInvariant();
        DataTable dataTable = new DataTable();

        if (ext == ".csv")
        {
            // 读取CSV
             Encoding encoding = DetectFileEncoding(filePath);
            using (var reader = new StreamReader(filePath, encoding))
            {
                bool isHeader = true;
                while (!reader.EndOfStream)
                {
                    var line = reader.ReadLine();
                    if (string.IsNullOrWhiteSpace(line)) continue;
                    var values = line.Split(',');

                    if (isHeader)
                    {
                        foreach (var col in values)
                            dataTable.Columns.Add(col.Trim());
                        isHeader = false;
                    }
                    else
                    {
                        var row = dataTable.NewRow();
                        for (int i = 0; i < dataTable.Columns.Count; i++)
                            row[i] = values[i].Trim();
                        dataTable.Rows.Add(row);
                    }
                }
            }
        }
        else if (ext == ".xls" || ext == ".xlsx")
        {
            using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                IWorkbook workbook;
                if (ext == ".xls")
                    workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(stream);
                else
                    workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(stream);

                var sheet = workbook.GetSheetAt(0);
                var headerRow = sheet.GetRow(0);
                int cellCount = headerRow.LastCellNum;

                for (int i = 0; i < cellCount; i++)
                {
                    var cell = headerRow.GetCell(i);
                    if (cell != null)
                        dataTable.Columns.Add(cell.ToString());
                }

                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    var row = sheet.GetRow(i);
                    if (row == null) continue;
                    var dataRow = dataTable.NewRow();
                    for (int j = 0; j < cellCount; j++)
                    {
                        var cell = row.GetCell(j);
                        dataRow[j] = cell?.ToString() ?? string.Empty;
                    }
                    dataTable.Rows.Add(dataRow);
                }
            }
        }
        else
        {
            throw new NotSupportedException("仅支持xls、xlsx、csv文件。");
        }

        return dataTable;
    }
}